﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class ServicesSheet_StatUseCarFeeByDep : System.Web.UI.Page
{
    private Srvtools.WebDataSet WMaster;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DSelYearMonth1.SelectYear = DateTime.Now.Year.ToString();
            DSelYearMonth1.SelectMonth = DateTime.Now.Month.ToString();
            SetDataSource(false);

            MasterPage_MasterPage master = (MasterPage_MasterPage)this.Page.Master;
            master.HelpHref = "~/Help/ServicesSheet/按部门统计车辆费用.htm";
            master.PageName = "按部门统计车辆费用";
            master.PicUrl = "";
            master.IntiBar();

        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SetDataSource(true);
    }

    private void SetDataSource(bool dIsPostback)
    {
        string Sql = "";
        string strYear = "";
        string strMonth = "";
        //string strDep = "";

        if (dIsPostback == true)
        {
            strYear = DSelYearMonth1.SelectYear;
            strMonth = DSelYearMonth1.SelectMonth;

            if (strYear == "0" || strYear == string.Empty) strYear = "";
            if (strYear.Length == 0)
            {
                strMonth = "";
            }
            else
            {
                if (strMonth == "0" || strMonth == string.Empty) strMonth = "";
            }
        }
        else
        {
            strYear = DateTime.Now.Year.ToString();
            strMonth = DateTime.Now.Month.ToString();
        }

        Sql = Sql + " SELECT DepartmentId, DepartmentName,";

        Sql = Sql + " (SELECT SUM(Services_UseCars.UseKil)";
        Sql = Sql + "  FROM Services_UseCars INNER JOIN";
        Sql = Sql + "  Services_SV_Cars ON ";
        Sql = Sql + "  Services_UseCars.CarId = Services_SV_Cars.CarId INNER JOIN";
        Sql = Sql + "  Employees ON ";
        Sql = Sql + "  Services_UseCars.UserId = Employees.EmployeeID";
        Sql = Sql + "  WHERE (Employees.DepartmentId = Department.DepartmentId)";
        if (strYear.Length > 0) Sql = Sql + string.Format(" AND (YEAR(Services_UseCars.UCDate) = {0}) ", strYear);
        if (strMonth.Length > 0) Sql = Sql + string.Format(" AND (MONTH(Services_UseCars.UCDate) = {0})", strMonth);
        Sql = Sql + "  GROUP BY Employees.DepartmentId) AS AllKill,";

        Sql = Sql + " (SELECT SUM(Services_UseCars.SupExpenses)";
        Sql = Sql + "  FROM Services_UseCars INNER JOIN";
        Sql = Sql + "  Services_SV_Cars ON ";
        Sql = Sql + "  Services_UseCars.CarId = Services_SV_Cars.CarId INNER JOIN";
        Sql = Sql + "  Employees ON ";
        Sql = Sql + "  Services_UseCars.UserId = Employees.EmployeeID";
        Sql = Sql + "  WHERE (Employees.DepartmentId = Department.DepartmentId)";
        if (strYear.Length > 0) Sql = Sql + string.Format(" AND (YEAR(Services_UseCars.UCDate) = {0}) ", strYear);
        if (strMonth.Length > 0) Sql = Sql + string.Format(" AND (MONTH(Services_UseCars.UCDate) = {0})", strMonth);
        Sql = Sql + "  GROUP BY Employees.DepartmentId) AS AllSExpenses,";

        Sql = Sql + "  (SELECT SUM(Services_UseCars.UseKil * Services_SV_Cars.PerKMCost)";
        Sql = Sql + "  FROM Services_UseCars INNER JOIN";
        Sql = Sql + "   Services_SV_Cars ON ";
        Sql = Sql + "   Services_UseCars.CarId = Services_SV_Cars.CarId INNER JOIN";
        Sql = Sql + "   Employees ON ";
        Sql = Sql + "   Services_UseCars.UserId = Employees.EmployeeID";
        Sql = Sql + "   WHERE (Employees.DepartmentId = Department.DepartmentId)";
        if (strYear.Length > 0) Sql = Sql + string.Format(" AND (YEAR(Services_UseCars.UCDate) = {0}) ", strYear);
        if (strMonth.Length > 0) Sql = Sql + string.Format(" AND (MONTH(Services_UseCars.UCDate) = {0})", strMonth);
        Sql = Sql + "  GROUP BY Employees.DepartmentId) AS Amount";

        Sql = Sql + "  FROM Department";
        Sql = Sql + "  WHERE (StatCarFee=1) And  (Del=0) ";
        // if (strDep.Length > 0) Sql = Sql + string.Format ("  And (DepartmentId Like '{0}%')",strDep);
        Sql = Sql + "  ORDER BY DepartmentId "; //DESC

        DataSet Tmp;
        InitializeComponent();
        Tmp = WMaster.Execute(Sql);

        DataTable svTable = Tmp.Tables[0];
        int AllKillCount = 0;
        Single AllAmount = 0;
        int AllExpenses = 0;

        for (int i = 0; i < svTable.Rows.Count; i++)
        {
            int AKil = (svTable.Rows[i]["AllKill"] == Convert.DBNull) ? 0 : Convert.ToInt32(svTable.Rows[i]["AllKill"]);
            int ASExpenses = (svTable.Rows[i]["AllSExpenses"] == Convert.DBNull) ? 0 : Convert.ToInt32(svTable.Rows[i]["AllSExpenses"]);
            Single Amout = (svTable.Rows[i]["Amount"] == Convert.DBNull) ? 0 : Convert.ToSingle(svTable.Rows[i]["Amount"]);

            AllKillCount = AllKillCount + AKil;
            AllExpenses = AllExpenses + ASExpenses;
            AllAmount = AllAmount + Amout;
        }

        DataRow SR;
        SR = svTable.NewRow();

        SR["DepartmentName"] = "合计:";
        SR["AllKill"] = AllKillCount;
        SR["AllSExpenses"] = AllExpenses;
        SR["Amount"] = AllAmount;

        svTable.Rows.Add(SR);

        wgvMaster.DataSource = svTable;
        wgvMaster.DataBind();

    }

    private void InitializeComponent()
    {
        System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(ServicesSheet_StatUseCarFeeByDep));
        this.WMaster = new Srvtools.WebDataSet();
        ((System.ComponentModel.ISupportInitialize)(this.WMaster)).BeginInit();
        // 
        // WMaster
        // 
        this.WMaster.Active = true;
        this.WMaster.AlwaysClose = false;
        this.WMaster.DeleteIncomplete = true;
        this.WMaster.Guid = null;
        this.WMaster.LastKeyValues = null;
        this.WMaster.Locale = new System.Globalization.CultureInfo("zh-CN");
        this.WMaster.PacketRecords = 100;
        this.WMaster.Position = -1;
        this.WMaster.RemoteName = "ServiceSheet.SV_StatEmp";
        this.WMaster.ServerModify = false;
        ((System.ComponentModel.ISupportInitialize)(this.WMaster)).EndInit();

    }
}
